package de.fhkl.strokeserver.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import de.fhkl.strokeserver.user.Admin;
import de.fhkl.strokeserver.user.Doctor;
import de.fhkl.strokeserver.user.Paramedic;
import de.fhkl.strokeserver.user.User;
import de.fhkl.strokeserver.util.Utilities;

public class MySQLConnection
{
	/** Init logger for this class */
	private static Logger sLogger = LoggerFactory.getLogger(MySQLConnection.class);

	/**
	 * sConnection holds the only instance of Connection (Singleton pattern!)
	 * 
	 * @see getInstance()
	 */
	private static volatile Connection sConnection = null;

	/** Lock object */
	private static Object sLock = new Object();

	/** Host name, default: localhost */
	private static String sDbHost;

	/** Port; default: 3306 */
	private static int sDbPort;

	/** Database name */
	private static String sDatabase;

	/** Database user */
	private static String sDbUser;

	/** Database password */
	private static String sDbPassword;

	/**
	 * 
	 */
	private MySQLConnection()
	{
		try
		{
			Properties properties = new Properties();
			properties.load(StrokeServer.class.getClassLoader().getResourceAsStream("config.properties"));

			sDbHost = properties.getProperty("db_host", "localhost");
			sDbPort = Integer.parseInt(properties.getProperty("db_port", "3306"));
			sDatabase = properties.getProperty("db_name", "");
			sDbUser = properties.getProperty("db_user", "");
			sDbPassword = properties.getProperty("db_password", "");
		}
		catch (Exception e)
		{
			sLogger.error("Couldn't load database settings." + e.getMessage());
			return;
		}

		try
		{
			// load database driver
			Class.forName("com.mysql.jdbc.Driver");

			// open connection
			sConnection = DriverManager.getConnection("jdbc:mysql://" + sDbHost + ":" + sDbPort + "/" + sDatabase + "?" + "user="
					+ sDbUser + "&" + "password=" + sDbPassword);
		}
		catch (ClassNotFoundException e)
		{
			sLogger.error("Database driver not found: " + e.getMessage());
		}
		catch (SQLException e)
		{
			sLogger.error("Unable to open a connection to the database: " + e.getMessage());
		}
	}

	/**
	 * 
	 * @return a MySQLConnection
	 */
	private static Connection getInstance()
	{
		if (sConnection == null)
		{
			synchronized (sLock)
			{
				if (sConnection == null)
				{
					new MySQLConnection();
				}
			}
		}

		return sConnection;
	}

	public static List<User> getAllUsers()
	{
		List<User> allUsers = new ArrayList<User>();
		sConnection = getInstance();

		String sql = "SELECT id, email, password, type FROM user";

		try
		{
			Statement st = sConnection.createStatement();
			ResultSet rs = st.executeQuery(sql);
			while (rs.next())
			{
				User user = null;
				String dbId = rs.getString(1);
				String dbEmail = rs.getString(2);
				String dbPassword = rs.getString(3);
				String dbType = rs.getString(4);
				dbType = dbType.toLowerCase();

				if (dbType.equals("d"))
				{
					user = new Doctor(UUID.fromString(dbId), dbEmail, dbPassword);
				}
				else if (dbType.equals("p"))
				{
					user = new Paramedic(UUID.fromString(dbId), dbEmail, dbPassword);
				}
				else if (dbType.equals("a"))
				{
					user = new Admin(UUID.fromString(dbId), dbEmail, dbPassword);
				}

				allUsers.add(user);
			}

			st.close();
			rs.close();
			sConnection.close();
			sConnection = null;
		}
		catch (Exception e)
		{
			sLogger.error(e.getMessage());
		}

		return allUsers;
	}

	/**
	 * Search for a user with the given credentials.
	 * 
	 * @param email
	 * @param password
	 *            MD-5 encrypted password
	 * @param type
	 * 
	 * @return user or null if no user found
	 */
	public static User getUser(String email, String password, char type)
	{
		User user = null;

		if (email != null && !email.isEmpty() && password != null && !password.isEmpty())
		{
			sConnection = getInstance();

			String sql = "SELECT id, email, password, type FROM user WHERE email = ? AND password = ? AND type = ?";

			try
			{
				PreparedStatement ps = sConnection.prepareStatement(sql);
				ps.setString(1, email);
				ps.setString(2, password);
				ps.setString(3, String.valueOf(type));

				ResultSet rs = ps.executeQuery();
				if (rs.next())
				{
					String dbId = rs.getString(1);
					String dbEmail = rs.getString(2);
					String dbPassword = rs.getString(3);
					String dbType = rs.getString(4);
					dbType = dbType.toLowerCase();

					if (dbType.equals("d"))
					{
						user = new Doctor(UUID.fromString(dbId), dbEmail, dbPassword);
					}
					else if (dbType.equals("p"))
					{
						user = new Paramedic(UUID.fromString(dbId), dbEmail, dbPassword);
					}
					else if (dbType.equals("a"))
					{
						user = new Admin(UUID.fromString(dbId), dbEmail, dbPassword);
					}
				}

				ps.close();
				sConnection.close();
				sConnection = null;
			}
			catch (SQLException s)
			{
				sLogger.error("SQL statement is not executed: " + s.getMessage());
			}
			catch (Exception e)
			{
				sLogger.error(e.getMessage());
			}
		}

		return user;
	}

	/**
	 * Checks if the email is in use
	 * 
	 * @param email
	 * 
	 * @return true if email is in use, false otherwise
	 */
	public static boolean isEmailInUse(String email)
	{
		boolean isInUse = false;

		if (email != null && !email.isEmpty())
		{
			sConnection = getInstance();

			String sql = "SELECT email FROM user WHERE email = ?";

			try
			{
				PreparedStatement ps = sConnection.prepareStatement(sql);
				ps.setString(1, email);

				ResultSet rs = ps.executeQuery();
				if (rs.next())
				{
					isInUse = true;
				}

				ps.close();
				sConnection.close();
				sConnection = null;
			}
			catch (SQLException s)
			{
				sLogger.error("SQL statement is not executed: " + s.getMessage());
			}
			catch (Exception e)
			{
				sLogger.error(e.getMessage());
			}
		}

		return isInUse;
	}

	/**
	 * Search for an activityLog.
	 * 
	 * @param recordingId
	 * 
	 * @return activityLog or null if no activityLog found
	 */
	public static ActivityLog getActivityLog(UUID recordingId)
	{
		ActivityLog activityLog = null;

		sConnection = getInstance();

		String sql = "SELECT id, paramedic_id, rec_started, rec_stopped, video_transmission_completed, "
				+ "doctor_id, video_accepted, video_started, video_rated, rating, rating_accepted FROM activity WHERE id = ?";

		try
		{
			PreparedStatement ps = sConnection.prepareStatement(sql);
			ps.setString(1, recordingId.toString());

			ResultSet rs = ps.executeQuery();
			if (rs.next())
			{
				UUID dbId = UUID.fromString(rs.getString(1));
				UUID dbParamedicId = UUID.fromString(rs.getString(2));
				Date dbRecordingStarted = rs.getTimestamp(3);
				Date dbRecordingStopped = rs.getTimestamp(4);
				Date dbVideoTransmissionCompleted = rs.getTimestamp(5);
				UUID dbDoctorId = UUID.fromString(rs.getString(6));
				Date dbVideoAccepted = rs.getTimestamp(7);
				Date dbVideoStarted = rs.getTimestamp(8);
				Date dbVideoRated = rs.getTimestamp(9);
				int dbRating = rs.getInt(10);
				Date dbRatingAccepted = rs.getTimestamp(11);

				activityLog = new ActivityLog();
				activityLog.setRecordingId(dbId);
				activityLog.setParamedicId(dbParamedicId);
				activityLog.setRecordingStarted(dbRecordingStarted);
				activityLog.setRecordingStopped(dbRecordingStopped);
				activityLog.setVideoTransmissionCompleted(dbVideoTransmissionCompleted);
				activityLog.setDoctorId(dbDoctorId);
				activityLog.setVideoAccepted(dbVideoAccepted);
				activityLog.setVideoStarted(dbVideoStarted);
				activityLog.setVideoRated(dbVideoRated);

				switch (dbRating)
				{
					case 0:
						activityLog.setRating(false);
					break;

					case 1:
						activityLog.setRating(true);
					break;

					default:
					break;
				}

				activityLog.setRatingAccepted(dbRatingAccepted);

			}

			ps.close();
			sConnection.close();
			sConnection = null;
		}
		catch (SQLException s)
		{
			sLogger.error("SQL statement is not executed: " + s.getMessage());
		}
		catch (Exception e)
		{
			sLogger.error(e.getMessage());
		}

		return activityLog;
	}

	/**
	 * Inserts an activityLog.
	 * 
	 * @param activityLog
	 * 
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements,<br>
	 *         (2) 0 for SQL statements that return nothing or<br>
	 *         (3) -1 that an exception occurred
	 */
	public static int insertActivityLog(ActivityLog activityLog)
	{
		int rowCount = -1;

		sConnection = getInstance();

		String sql = "INSERT INTO activity (id, paramedic_id, rec_started, rec_stopped, video_transmission_completed, "
				+ "doctor_id, video_accepted, video_started, video_rated, rating, rating_accepted) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		try
		{
			PreparedStatement ps = sConnection.prepareStatement(sql);
			ps.setString(1, activityLog.getRecordingId().toString());
			ps.setString(2, activityLog.getParamedicId().toString());
			ps.setTimestamp(3, new Timestamp(activityLog.getRecordingStarted().getTime()));

			if (activityLog.getRecordingStopped() != null)
			{
				ps.setTimestamp(4, new Timestamp(activityLog.getRecordingStopped().getTime()));
			}
			else
			{
				ps.setTimestamp(4, null);
			}

			if (activityLog.getVideoTransmissionCompleted() != null)
			{
				ps.setTimestamp(5, new Timestamp(activityLog.getVideoTransmissionCompleted().getTime()));
			}
			else
			{
				ps.setTimestamp(5, null);
			}

			if (activityLog.getDoctorId() != null)
			{
				ps.setString(6, activityLog.getDoctorId().toString());
			}
			else
			{
				ps.setString(6, null);
			}

			if (activityLog.getVideoAccepted() != null)
			{
				ps.setTimestamp(7, new Timestamp(activityLog.getVideoAccepted().getTime()));
			}
			else
			{
				ps.setTimestamp(7, null);
			}

			if (activityLog.getVideoStarted() != null)
			{
				ps.setTimestamp(8, new Timestamp(activityLog.getVideoStarted().getTime()));
			}
			else
			{
				ps.setTimestamp(8, null);
			}

			if (activityLog.getVideoRated() != null)
			{
				ps.setTimestamp(9, new Timestamp(activityLog.getVideoRated().getTime()));
			}
			else
			{
				ps.setTimestamp(9, null);
			}

			if (activityLog.isRating() != null)
			{
				ps.setInt(10, (activityLog.isRating().booleanValue() ? 1 : 0));
			}
			else
			{
				ps.setInt(10, -1);
			}

			if (activityLog.getRatingAccepted() != null)
			{
				ps.setTimestamp(11, new Timestamp(activityLog.getRatingAccepted().getTime()));
			}
			else
			{
				ps.setTimestamp(11, null);
			}

			rowCount = ps.executeUpdate();

			ps.close();
			sConnection.close();
			sConnection = null;
		}
		catch (SQLException s)
		{
			sLogger.error("SQL statement is not executed: " + s.getMessage());
		}
		catch (Exception e)
		{
			sLogger.error(e.getMessage());
		}
		return rowCount;
	}

	/**
	 * Updates an activityLog.
	 * 
	 * @param activityLog
	 * 
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements,<br>
	 *         (2) 0 for SQL statements that return nothing or<br>
	 *         (3) -1 that an exception occurred
	 */
	public static int updateActivityLog(ActivityLog activityLog)
	{
		int rowCount = -1;

		sConnection = getInstance();

		String sql = "UPDATE activity SET paramedic_id = ?, rec_started = ?, rec_stopped = ?, video_transmission_completed = ?, "
				+ "doctor_id = ?, video_accepted = ?, video_started = ?, video_rated = ?, rating = ?, rating_accepted = ? WHERE id = ?";

		try
		{
			PreparedStatement ps = sConnection.prepareStatement(sql);
			ps.setString(1, activityLog.getParamedicId().toString());
			ps.setTimestamp(2, new Timestamp(activityLog.getRecordingStarted().getTime()));

			if (activityLog.getRecordingStopped() != null)
			{
				ps.setTimestamp(3, new Timestamp(activityLog.getRecordingStopped().getTime()));
			}
			else
			{
				ps.setTimestamp(3, null);
			}

			if (activityLog.getVideoTransmissionCompleted() != null)
			{
				ps.setTimestamp(4, new Timestamp(activityLog.getVideoTransmissionCompleted().getTime()));
			}
			else
			{
				ps.setTimestamp(4, null);
			}

			if (activityLog.getDoctorId() != null)
			{
				ps.setString(5, activityLog.getDoctorId().toString());
			}
			else
			{
				ps.setString(5, null);
			}

			if (activityLog.getVideoAccepted() != null)
			{
				ps.setTimestamp(6, new Timestamp(activityLog.getVideoAccepted().getTime()));
			}
			else
			{
				ps.setTimestamp(6, null);
			}

			if (activityLog.getVideoStarted() != null)
			{
				ps.setTimestamp(7, new Timestamp(activityLog.getVideoStarted().getTime()));
			}
			else
			{
				ps.setTimestamp(7, null);
			}

			if (activityLog.getVideoRated() != null)
			{
				ps.setTimestamp(8, new Timestamp(activityLog.getVideoRated().getTime()));
			}
			else
			{
				ps.setTimestamp(8, null);
			}

			if (activityLog.isRating() != null)
			{
				ps.setInt(9, (activityLog.isRating().booleanValue() ? 1 : 0));
			}
			else
			{
				ps.setInt(9, -1);
			}

			if (activityLog.getRatingAccepted() != null)
			{
				ps.setTimestamp(10, new Timestamp(activityLog.getRatingAccepted().getTime()));
			}
			else
			{
				ps.setTimestamp(10, null);
			}
			ps.setString(11, activityLog.getRecordingId().toString());

			rowCount = ps.executeUpdate();

			ps.close();
			sConnection.close();
			sConnection = null;
		}
		catch (SQLException s)
		{
			sLogger.error("SQL statement is not executed: " + s.getMessage());
		}
		catch (Exception e)
		{
			sLogger.error(e.getMessage());
		}
		return rowCount;
	}

	/**
	 * Inserts an user.
	 * 
	 * @param email
	 * @param password
	 * @param type
	 * 
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements,<br>
	 *         (2) 0 for SQL statements that return nothing or<br>
	 *         (3) -1 that an exception occurred
	 */
	public static int insertUser(String email, String password, String type)
	{
		int rowCount = -1;

		if (!isEmailInUse(email))
		{
			sConnection = getInstance();

			try
			{
				String sql = "INSERT INTO user (id, email, password, type) VALUES (?, ?, ?, ?)";
				PreparedStatement ps = sConnection.prepareStatement(sql);
				ps.setString(1, UUID.randomUUID().toString());
				ps.setString(2, email);
				String md5 = Utilities.generateMd5Sum(password);
				ps.setString(3, md5);
				ps.setString(4, type);

				rowCount = ps.executeUpdate();

				ps.close();
				sConnection.close();
				sConnection = null;
			}
			catch (SQLException s)
			{
				sLogger.error("SQL statement is not executed: " + s.getMessage());
			}
			catch (Exception e)
			{
				sLogger.error(e.getMessage());
			}
		}
		else
		{
			rowCount = 0;
		}

		return rowCount;
	}

	/**
	 * Deletes an user.
	 * 
	 * @param id
	 * 
	 * @return either (1) the row count for SQL Data Manipulation Language (DML)
	 *         statements,<br>
	 *         (2) 0 for SQL statements that return nothing or<br>
	 *         (3) -1 that an exception occurred
	 */
	public static int deleteUser(String id)
	{
		int rowCount = -1;

		sConnection = getInstance();

		String sql = "DELETE FROM user WHERE id = ?";

		try
		{
			PreparedStatement ps = sConnection.prepareStatement(sql);
			ps.setString(1, id);

			rowCount = ps.executeUpdate();

			ps.close();
			sConnection.close();
			sConnection = null;
		}
		catch (SQLException s)
		{
			sLogger.error("SQL statement is not executed: " + s.getMessage());
		}
		catch (Exception e)
		{
			sLogger.error(e.getMessage());
		}
		return rowCount;
	}
}
